<!DOCTYPE html>
<html>
<head>
    <title>005.数据的增删改</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="selected">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 005.数据的增删改 </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">005.数据的增删改</a>
<ul>
<li>
<a href="#toc_1">增加数据</a>
<ul>
<li>
<a href="#toc_2">不指定列名插入数据,</a>
</li>
<li>
<a href="#toc_3">指定列名插入值</a>
</li>
<li>
<a href="#toc_4">一次插入多条记录</a>
</li>
<li>
<a href="#toc_5">复制表结构与复制表中数据</a>
</li>
<li>
<a href="#toc_6">复制数据到别的表.</a>
</li>
</ul>
</li>
<li>
<a href="#toc_7">删除数据 DELETE FROM  | 清空:TRUNCATE TABLE</a>
</li>
<li>
<a href="#toc_8">修改(更新)数据 UPDATE</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">增加数据</h2>

<blockquote>

<p class="md_block">
    <span class="md_line">注意: <br /></span>
    <span class="md_line">1. 字符串类型的值与时间日期的值必须加上单引号.<br /></span>
    <span class="md_line">2. SQL SERVER 数据库名,表名,字段名都支持中文,但尽量不要使用.</span>
</p>

</blockquote>

<h3 id="toc_2">不指定列名插入数据,</h3>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">特点:</span>
</p>


<ol>
<li>自增列不能指定值,尽量要把自增列放在第一列.</li>
<li>除了自增列,其它所有列要按列的顺序依次插入值.</li>
</ol>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 不指定列名插入值</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="err">表名</span> <span class="k">VALUES</span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="err">值</span><span class="mi">2</span><span class="p">,</span><span class="err">值</span><span class="mi">3</span><span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_3">指定列名插入值</h3>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">特点:</span>
</p>


<ol>
<li>指定列名时可以随意个数,随意顺序.VALUES中的值与指定的列名对应就好.</li>
<li>非空列(NOT NULL),且不是自增或有默认值时,必须要插入值.</li>
</ol>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 指定列名插入值</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="err">表名</span><span class="p">(</span><span class="err">列名</span><span class="mi">1</span><span class="p">,</span><span class="err">列名</span><span class="mi">2</span><span class="p">,</span><span class="err">列名</span><span class="mi">3</span><span class="p">,....)</span> <span class="k">VALUES</span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="err">值</span><span class="mi">2</span><span class="p">,</span><span class="err">值</span><span class="mi">3</span><span class="p">,....);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_4">一次插入多条记录</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 在VALUES后面根多个小括号就行.</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="err">表名</span> <span class="k">VALUES</span><span class="p">(</span><span class="s1">&#39;张三&#39;</span><span class="p">,</span><span class="s1">&#39;18&#39;</span><span class="p">,</span><span class="mi">1</span><span class="p">),(</span><span class="s1">&#39;李四&#39;</span><span class="p">,</span><span class="mi">16</span><span class="p">,</span><span class="mi">0</span><span class="p">),(</span><span class="s1">&#39;王五&#39;</span><span class="p">,</span><span class="mi">14</span><span class="p">,</span><span class="mi">1</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t1</span><span class="p">(</span><span class="n">age</span><span class="p">,</span><span class="n">uname</span><span class="p">)</span> <span class="k">VALUES</span><span class="p">(</span><span class="mi">20</span><span class="p">,</span><span class="s1">&#39;王二麻子&#39;</span><span class="p">),(</span><span class="mi">22</span><span class="p">,</span><span class="s1">&#39;大麻子&#39;</span><span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_5">复制表结构与复制表中数据</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 复制表结构与数据,注意不带任何除(自增与非空)的约束.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">INTO</span> <span class="err">新表名</span> <span class="k">FROM</span> <span class="err">表名</span><span class="p">;</span>
<span class="c1">-- 只复制一个表结构,不带数据,写个不成立的条件就行</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">INTO</span> <span class="n">temp1</span> <span class="k">FROM</span> <span class="n">stu_info</span> <span class="k">WHERE</span> <span class="mi">1</span> <span class="o">=</span> <span class="mi">2</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_6">复制数据到别的表.</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">temp1</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stu_info</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_7">删除数据 DELETE FROM  | 清空:TRUNCATE TABLE</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 注意,以后删除语必须加条件where</span>
<span class="k">DELETE</span> <span class="k">FROM</span> <span class="err">表名</span><span class="p">;</span>
<span class="k">DELETE</span> <span class="k">FROM</span> <span class="err">表名</span> <span class="p">[</span><span class="k">WHERE</span> <span class="err">条件</span><span class="p">]</span> <span class="p">[</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="err">字段名</span><span class="p">]</span> <span class="p">[</span><span class="k">LIMIT</span> <span class="err">限制条数</span><span class="p">]</span>
<span class="c1">-- 彻底请空数据表()</span>
<span class="k">TRUNCATE</span> <span class="k">TABLE</span> <span class="err">表名</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">注意:delete是删除数据,可以全部删除,也可以只删除一部分行.但truncate则不同,它相当于把表数据清空,而且想当于初始化表.用带自增列的表去测试,分别用delete from 与 truncate;</span>
</p>

</blockquote>

<h2 id="toc_8">修改(更新)数据 UPDATE</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 注意,以后修改语必须加条件where</span>
<span class="k">UPDATE</span> <span class="err">表名</span> <span class="k">SET</span> <span class="err">列名</span> <span class="o">=</span> <span class="err">值</span><span class="p">,.....</span> <span class="p">[</span><span class="k">WHERE</span> <span class="err">条件</span><span class="p">]</span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">注:WHERE后的条件都放到查询中讲.</span>
</p>

</blockquote> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>